package com.rg1906.dao.Impl;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.rg1906.entity.QuerygradeClass;
import com.rg1906.entity.StudentScore;
import com.rg1906.utils.DataSourceC3P0ThreadLocal;

public class StudentScoreDaoImpl {
	DataSource dataSource = DataSourceC3P0ThreadLocal.getDataSource();
	QueryRunner qr = new QueryRunner(dataSource);
	public List<StudentScore> GetScore(String pageNumber, String pageSize, String gid, String cid, int rid, String likeName, String likeGradeName, String likeGradeClass) throws SQLException {
		String string=null;
		int pageNumberInt = 0;
		int pageSizeInt=0;
		if(pageNumber!=null&&pageSize!=null) {
			pageNumberInt= Integer.parseInt(pageNumber);
			pageSizeInt= Integer.parseInt(pageSize);
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT ");
			sb.append("distinct ");
			sb.append("s.id,");
			sb.append("s.realname,");
			sb.append("s.studentNum,");
			sb.append("s.gid,");
			sb.append("s.cid,");
			sb.append("CONCAT( grade.gradename, class.gradename ) gradeClass,");
			sb.append("SUM( CASE c.courseName WHEN '语文' THEN sc.score ELSE 0 END ) ChineseScore,");
			sb.append("SUM( CASE c.courseName WHEN '数学' THEN sc.score ELSE 0 END ) MathScore,");
			sb.append("SUM( CASE c.courseName WHEN '英语' THEN sc.score ELSE 0 END ) EnglishScore,");
			sb.append("SUM( CASE c.courseName WHEN '化学' THEN sc.score ELSE 0 END ) ChemistryScore,");
			sb.append("SUM( CASE c.courseName WHEN '物理' THEN sc.score ELSE 0 END ) PhysicsScore,");
			sb.append("SUM( CASE c.courseName WHEN '生物' THEN sc.score ELSE 0 END ) BiologyScore,");
			sb.append("SUM( sc.score ) count,");
			sb.append("count( * ) total ");
			sb.append("FROM ");
			sb.append("t_score sc ");
			sb.append("LEFT JOIN t_student s ON sc.studentNum = s.studentNum ");
			sb.append("LEFT JOIN t_course c ON sc.courseNum = c.courseNum ");
			sb.append("LEFT JOIN t_grade grade ON s.gid = grade.id ");
			sb.append("LEFT JOIN t_grade class ON s.cid = class.id ");
			if(gid!=null&&cid!=null&&rid==4) {
				sb.append("WHERE grade.id ="+gid+" and class.id = "+cid+" ");
			}else if(gid!=null&&rid==3) {
				sb.append("WHERE grade.id ="+gid+" ");
			}else if(rid==1) {
				sb.append("where 1=1 ");
			}
			if(likeName!=null) {
				sb.append("and s.realname Like '%"+likeName+"%' ");
			}
			if(likeGradeName!=null) {
				sb.append("and grade.gradename Like '"+likeGradeName+"' ");
			}
			if(likeGradeClass!=null) {
				sb.append("and class.gradename Like '"+likeGradeClass+"' ");
			}
			sb.append("GROUP BY ");
			sb.append("sc.studentNum ");
			sb.append("HAVING ");
			sb.append("gradeClass is not null ");
			
			sb.append("limit ");
			sb.append("?,?");
			 string = sb.toString();
		}
		Object[] par= {pageNumberInt*pageSizeInt,pageSizeInt};
	 List<StudentScore> list = qr.query(string, new BeanListHandler<>(StudentScore.class),par);
	 return list;
	}
	public Long GetCount(String gid, String cid, String likeName, int rid, String likeGradeName, String likeGradeClass) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("count(DISTINCT sc.studentNum) ");
		sb.append("FROM ");
		sb.append("t_score sc ");
		sb.append("LEFT JOIN t_student s ON sc.studentNum = s.studentNum ");
		sb.append("LEFT JOIN t_course c ON sc.courseNum = c.courseNum ");
		sb.append("LEFT JOIN t_grade grade ON s.gid = grade.id ");
		sb.append("LEFT JOIN t_grade class ON s.cid = class.id ");
		if(gid!=null&&cid!=null&&rid==4) {
			sb.append("WHERE grade.id ="+gid+" and class.id = "+cid+" ");
		}else if(gid!=null&&rid==3) {
			sb.append("WHERE grade.id ="+gid+" ");
		}else if(rid==1) {
			sb.append("where 1=1 ");
		}
		if(likeName!=null) {
			sb.append("and s.realname Like '%"+likeName+"%' ");
		}
		if(likeGradeName!=null) {
			sb.append("and grade.gradename Like '%"+likeGradeName+"%' ");
		}
		if(likeGradeClass!=null) {
			sb.append("and class.gradename Like '%"+likeGradeClass+"%' ");
		}
		return qr.query(sb.toString(), new ScalarHandler<>());
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	public String queryGradeName(String gid) throws SQLException {//查找年级的方法
		String sql="select gradename from t_grade where id=? and gid is null";
		String gradename = qr.query(sql, new ScalarHandler<>(),gid);
		return gradename;
	}
	
	public List<QuerygradeClass> queryGradeClass(String gid, String cid) throws SQLException {
		String sql="select gradename from t_grade where id=? and gid=?";
		Object[] par= {cid,gid};
		List<QuerygradeClass> list = qr.query(sql, new BeanListHandler<>(QuerygradeClass.class),par);
		return list;
		
	}
	public List<QuerygradeClass> queryGradeClass1(String gid) throws SQLException {
		String sql="select gradename from t_grade where gid=?";
		List<QuerygradeClass> list = qr.query(sql, new BeanListHandler<>(QuerygradeClass.class),gid);
		return list;
	}
}
